package com.edu.hre.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;



import com.edu.hre.db.config.BaseDAO;
import com.edu.hre.db.config.DBPool;
import com.edu.hre.db.dto.HreEmpEmergencyContact;
import com.edu.hre.db.dto.HreEmpDependent;
import com.edu.hre.db.dto.HreEmpJobs;


public class EmpJobsDAO  extends BaseDAO {
	private static final Logger logger = Logger.getLogger(EmpJobsDAO.class);
	@SuppressWarnings("deprecation")
	public List<HreEmpJobs> getJobsByEmployeeId(
			int employeeNumber) {
		List<HreEmpJobs> listJobs = new ArrayList<HreEmpJobs>();
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		try {
			conn = DBPool.getConnection();
			String strSQLQuery = "SELECT j.*, "+
					"t.job_title AS emp_job_title, " +
					"s.name AS emp_job_status, " +
					"su.name AS emp_job_sub_unit, " +
					"l.name AS emp_job_location " +
					"FROM hre_emp_jobs j " +
			"LEFT JOIN hre_job_title t ON j.job_title_id = t.id " +
			"LEFT JOIN hre_employment_status s ON j.emp_status_id = s.id " +
			"LEFT JOIN hre_subunit su ON j.sub_unit_id = su.id " +
			"LEFT JOIN hre_location l ON j.location_id = l.id " +
			"WHERE emp_number = " + employeeNumber;
			preStmt = conn.prepareStatement(strSQLQuery);
			rs = preStmt.executeQuery();
			while (rs.next()) {
				HreEmpJobs empJobs = new HreEmpJobs();
				empJobs.setJobTitle(rs.getString("emp_job_title"));
				empJobs.setJobEmpStatus(rs.getString("emp_job_status"));
				empJobs.setJobSubUnit(rs.getString("emp_job_sub_unit"));
				empJobs.setJobLocation(rs.getString("emp_job_location"));
				Date d = null;
				if(rs.getDate("contract_start_date") != null){
					d= (rs.getDate("contract_start_date"));
					d.setYear(d.getYear()-1900);
				}
				empJobs.setContractStartDate(d);
				if(rs.getDate("contract_end_date") != null){
					d= (rs.getDate("contract_end_date"));
					d.setYear(d.getYear()-1900);
				}
				empJobs.setContractEndDate(d);
				if(rs.getDate("joined_date") != null){
					d= (rs.getDate("joined_date"));
					d.setYear(d.getYear()-1900);
				}
				empJobs.setJoinedDate(d);
				empJobs.setTerminatedStatus(rs.getBoolean("terminated_status"));
				//.....
				//.....
				listJobs.add(empJobs);
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			// đóng PreparedStatement, ResultSet và đẩy connection vào pool
			releaseConnection(conn, preStmt, rs);
		}
		return listJobs;
	}

}
